from flask import request
from sqlalchemy import text

from models import db
from tools import hump_to_underline, underline_to_camel


class DbTool:
    """
    db工具
    """

    @staticmethod
    def get_condition_params():
        """
        获取自定义查询参数
        :return:
        """
        data = request.get_json()
        res = []
        for (key, value) in data.items():
            if key.startswith("m_"):
                arr = key.split("_")
                if len(arr) == 3:
                    res.append({
                        "table": None,
                        "op": arr[1],
                        "key": arr[2],
                        "value": value
                    })
                elif len(arr) == 4:
                    res.append({
                        "table": arr[1],
                        "op": arr[2],
                        "key": arr[3],
                        "value": value
                    })
        return res

    @staticmethod
    def filter_by_custom(model):
        """
        单表-自定义查询条件
        :param model:
        :return:
        """
        conditions = DbTool.get_condition_params()
        q = db.session.query(model)
        for item in conditions:
            key = item.get('key')
            if not hasattr(model, key):
                continue
            op = item.get('op', 'EQ')
            value = item.get('value')
            if op == 'EQ':
                q = q.filter(getattr(model, key) == value)
            elif op == 'NE':
                q = q.filter(getattr(model, key) != value)
            elif op == 'GT':
                q = q.filter(getattr(model, key) > value)
            elif op == 'GE':
                q = q.filter(getattr(model, key) >= value)
            elif op == 'LT':
                q = q.filter(getattr(model, key) < value)
            elif op == 'LE':
                q = q.filter(getattr(model, key) <= value)
            elif op == 'BT':
                if isinstance(value, list) and len(value) == 2:
                    q = q.filter(getattr(model, key).between(value[0], value[1]))
            elif op == 'LIKE':
                q = q.filter(getattr(model, key).like('%' + value + '%'))
            elif op == 'NLIKE':
                q = q.filter(getattr(model, key).notlike('%' + value + '%'))
            elif op == 'LLIKE':
                q = q.filter(getattr(model, key).like('%' + value))
            elif op == 'RLIKE':
                q = q.filter(getattr(model, key).like(value + '%'))
            elif op == 'IN':
                if isinstance(value, list):
                    q = q.filter(getattr(model, key).in_(value))
            elif op == 'NIN':
                if isinstance(value, list):
                    q = q.filter(getattr(model, key).notin_(value))
        # 增加逻辑删除查询条件
        isDeleted = getattr(model, "isDeleted")
        if isDeleted is not None: 
            q = q.filter(isDeleted == 0)
        return q

    @staticmethod
    def row_to_dict(row):
        res = {}
        for key in row.keys():
            res[key] = row[key]
        return res

    @staticmethod
    def select_one(sql, **kwargs):
        """
        查询单条记录
        :param sql: sql语句
        :param kwargs: 动态参数
        :return:
        """
        stmt = text(sql)
        with db.engine.connect() as conn:
            result = conn.execute(stmt, kwargs)
            row = result.first()
            return DbTool.row_to_dict(row)

    @staticmethod
    def select(sql, **kwargs):
        """
        查询多条记录
        :param sql: sql语句
        :param kwargs: 动态参数
        :return:
        """
        res = []
        stmt = text(sql)
        with db.engine.connect() as conn:
            result = conn.execute(stmt, kwargs)
            rows = result.fetchall()
            for row in rows:
                res.append(DbTool.row_to_dict(row))
            return res

    @staticmethod
    def select_page(sql, **kwargs):
        """
        查询分页记录
        :param sql: sql语句
        :param kwargs: 动态参数
        :return:
        """
        res = []
        stmt = text(f"{sql} LIMIT :page, :pageSize")
        if kwargs.get("pageNum") is None:
            kwargs['pageNum'] = 1
        if kwargs.get("pageSize") is None:
            kwargs['pageSize'] = 20
        kwargs['page'] = kwargs.get("pageNum")-1
        stmt_count = text(f"SELECT count(*) AS count_1 FROM ({sql}) AS anon_1")
        with db.engine.connect() as conn:
            count_result = conn.execute(stmt_count, kwargs)
            count = count_result.first()
            total = count['count_1']
            per_page = kwargs['pageSize']
            if total > 0:
                result = conn.execute(stmt, kwargs)
                rows = result.fetchall()
                for row in rows:
                    res.append(DbTool.row_to_dict(row))
            return {
                "recordCount": total,
                "totalPage": int((total - 1) / per_page) + 1,
                "pageSize": per_page,
                "pageNum": kwargs['pageNum'],
                "rows": res
            }
